Create HourCategory Column by following the steps below
Home >>>Transform
>>>
Power Query>>>
Select Zendesk Table
Duplicate Created Column and
Transform it to Hour>>>
Click Add column
>>>>
Custom Column and Add the syntax
below
if([Hour]) >7 and [Hour] <
11 then "7am-11am"
else if([Hour]) > 11 and
[Hour] <15 then "11am-3pm"
else if([Hour])> 15 and
[Hour] < 19 then "3pm-7pm"
else "7pm-7am"
Close and Apply
Follow the steps to build By Week chart visual
1. Drag and drop line and stacked column chart from visualizations.
Dimensions
General
X- 0,
Y-183,
Width -642,
Height 308
2. Drag and drop
· shared axis
For The Week and dayofweek from Calendar table
column series
· Hour category from ZendeskTicketSystem
values
· Avg tickets per day
Line value
· 1stcontactresolutionRate
3. Legend - on
·
Color - Black, 40% Lighter
·
Font family – Segoe UI
·
Text size - 9pt
·
Style - Markers only
4. X-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
5. Y-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
6. Secondary Y-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
7. Data colors
·
1stContResolutionRate- #002050
·
7AM-11AM- #CDD800
·
11AM-3AM- #9C8521
·
3PM-7PM-
#F98D09
· 7PM-7PM- #5A004F
Created a table that sorts the Color for Hour Category
Click on Hour and Sort by Order, Go to Model and create relationship with Zendesk table by Hour column
And Create a Calculated column on Zendesk Table to use it in the sort
HourSort = RELATED('Sort
Hour & Color'[Order])
8. Shapes -on
·
Shade area on
·
Stroke width -2
9. Title - on
·
Title text: By Week
·
Font Color: White
·
Background Color: #005AA1
·
Alignment: Center
·
Text size: 12pt
10. Boarder - on
Sort the Week
Transfom and open the Power Query
Duplicate the Created date and Transfom it to
Start of the week and
Rename WeekStart
Close and apply
Open the Data view
Select For the Week Column
Sort by Weekstart
Enter
§ once you do the formatting follow the GIF below to filter the graph by last 12 weeks.
Follow the Steps below to build Open and New Tickets Visual
1. Drag and drop line and stacked column chart from visualizations.
Dimensions
General x-642, Y-183, Width -638, Height 308
2. Select the Agent Name column and Right click and New Group to rename Zendesk Ticket as New Ticket
Drag and drop
shared axis
· Agent Name that is Created by the above Grouping
values
· Open tickets and new tickets Measures
Line values
·
Avg Days Open measure
3. Legend - on
·
Color - Black, 40% Lighter
·
Font family – Segoe UI
·
Text size - 9pt
·
Style - Markers only
4. X-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
5. Y-axis - on
·
Color - Black
·
Text size - 12pt
·
Font family - Segoe UI
·
Title - off
6. Secondary Y-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
7. Data colors
·
Avg days open - #5F3192
·
Open Tickets - #B4009E
·
New Tickets - #05F4F4
8. Shapes -on
·
Shade area on
·
Stroke width -2
9. Title - on
·
Title text: Open and New Ticket
·
Font Color: White
·
Background Color: #005AA1
·
Alignment: Center
·
Text size: 12pt
10. Boarder - on
· Color - #E6E6E6
Expand the Filter and add the Last 12 Weeks filter calculated column into the Filter tab
Follow the Steps below to Avg Tickets per Day Visual
1. Drag and drop line and stacked column chart from visualizations.
Dimensions
General x-0, Y-490, Width -643, Height 308
2. Drag and drop
· shared axis
Grouped Agent Name column from Zendesk table.
· values
Avg Tickets Per Day measure
Line values
· 1stcontactresolutionRate Measure
3. Legend - on
·
Color - Black, 40% Lighter
·
Font family – Segoe UI
·
Text size - 9pt
·
Style - Markers only
4. X-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
5. Y-axis - on
·
Color - Black
·
Text size - 12pt
·
Font family - Segoe UI
·
Title - off
6. Secondary Y-axis - on
·
Color - Black
·
Text size - 9pt
·
Font family - Segoe UI
·
Title - off
7. Data colors
·
First Contact Resolution Rate - #002050
·
Default Color - #CC7746
8. Shapes -on
·
Shade area on
·
Stroke width -2
9. Title - on
·
Title text: Tickets per Day
·
Font Color: White
·
Background Color: #005AA1
·
Alignment: Center
·
Text size: 12pt
10. Boarder - on
·
Color - #E6E6E6
Add filter as shown below in the screenshot in the Filters tab
Follow the Steps below to build By Hour visual
1. Drag and drop line an area chart from visualizations.
Dimension
General x-640, Y-490, Width -638, Height 308
2. Drag and drop
· axis
For The Week, dayofweek from Calendar and Hour from Zendesk
· values
Average First Response hours measure
· Line value
1stcontactresolutionRate measure
3. Legend - on
·
Color - Black
·
Font family – Segoe UI
·
Text size - 8pt
·
Style - Markers only
4. X-axis - on
·
Color - Black
·
Text size - 10pt
·
Font family - Segoe UI
·
Minimum category width-20
·
Maximum size-25%
·
Title - off
5. Y-axis - on
·
Color - Black
·
Text size - 12pt
·
Font family - Segoe UI
·
Title – off
o
Gridlines-on
·
Line color – white, 10% lighter
·
Line style- Solid
7. Secondary Y-axis - on
·
Color - Black
·
Text size - 12pt
·
Font family - Segoe UI
·
Title - off
8. Data colors
·
AvgFirstRspHrs- #669396
·
1stConRRate - #002050
·
60% Area Transparency
9. Data Labels
·
1stConRRate Color – Black
·
Text Size – 9pt
·
AvgFirstRspHrs color- #669396
·
Text Size – 9pt
10. Shapes -on
·
Stroke width -2
·
Join type – Round
·
Line style - solid
11. Title - on
·
Title text: Ticket performance by Hour
·
Font Color: White
·
Background Color: #005AA1
·
Alignment: Center
·
Text size: 12pt
·
• Font
family - Segoe UI
12. Boarder - on
Add filter in the Filter tab to display last 12 weeks only
How to create line and stacked column chart?